using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using Excel = Microsoft.Office.Interop.Excel;

namespace Glpt.Office
{
    /// <summary>
    /// 导出Excel类
    /// </summary>
    public class ExportExcel
    {
        private object[] objHeaders;
        private object[] objData;
        private string[] fieldNames;
        private object[] fieldData;
        private string[] columnFormat;
        private List<CellStyle> cellStyle = null;
        private int rowCount = 0;
        private object[] FieldTotals = null;
        private int depth = 1;
        /// <summary>
        /// 关键域
        /// </summary>
        public System.Collections.Hashtable KeyFlag = new System.Collections.Hashtable();
        /// <summary>
        /// 通用报表模板文件名
        /// </summary>
        public const string CommReportName = "CommReport.xlt";
        private Excel.Range m_objRange = null;
        private Excel.Font m_objFont = null;
        private object m_objOpt = null;
        private Excel.Application m_objExcel = null;
        private Excel.Workbooks m_objBooks = null;
        private Excel._Workbook m_objBook = null;
        private Excel.Sheets m_objSheets = null;
        private Excel._Worksheet m_objSheet = null;
        private string TempletFile = "";
        /// <summary>
        /// 保护密码
        /// </summary>
        public string ProtectPassWord = "";

        /// <summary>
        /// 初始化类的新实例
        /// </summary>
        public ExportExcel()
        {
            m_objOpt = System.Reflection.Missing.Value;
            m_objExcel = new Excel.Application();
            m_objExcel.DisplayAlerts = false;
            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
        }

        /// <summary>
        /// 初始化类的新实例
        /// </summary>
        /// <param name="templetFile">模版文件</param>
        public ExportExcel(string templetFile)
        {
            m_objOpt = System.Reflection.Missing.Value;
            m_objExcel = new Excel.Application();
            m_objExcel.DisplayAlerts = false;
            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            if (System.IO.File.Exists(templetFile))
            {
                TempletFile = templetFile;
                m_objBook = (Excel._Workbook)m_objBooks.Open(templetFile, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            }
            else
                m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
        }

        /// <summary>
        /// 设置数据源
        /// </summary>
        /// <param name="result">数据表</param>
        public void SetDataSource(DataTable result)
        {
            string[] columnname = new string[result.Columns.Count];
            for (int i = 0; i < columnname.Length; i++)
            {
                columnname[i] = result.Columns[i].ColumnName;
            }
            SetDataSource(result.DefaultView, columnname);
        }

        /// <summary>
        /// 设置数据源
        /// </summary>
        /// <param name="result">数据表</param>
        /// <param name="columnname">列名</param>
        public void SetDataSource(DataView result, string[] columnname)
        {
            string[] columnformat = new string[columnname.Length];
            for (int i = 0; i < columnformat.Length; i++)
            {
                if (result.Table.Columns[columnname[i]].DataType.IsValueType)
                    columnformat[i] = "N";
                else
                    columnformat[i] = "";
            }
            SetDataSource(result, columnname, columnname, columnformat, null, null);
        }

        /// <summary>
        /// 设置数据源
        /// </summary>
        /// <param name="result">数据表</param>
        /// <param name="RowFilter">过滤表达式</param>
        /// <param name="sort">排序表达式</param>
        /// <param name="columnname">列名</param>
        /// <param name="columnformat">列格式化</param>
        /// <param name="fieldTotals">汇总字段信息</param>
        public void SetDataSource(DataTable result, string RowFilter, string sort, string[] columnname, string[] columnformat, object[] fieldTotals)
        {
            DataView dv = result.DefaultView;
            dv.RowFilter = RowFilter;
            dv.Sort = sort;
            SetDataSource(dv, columnname, columnname, columnformat, fieldTotals, null);
        }
        /// <summary>
        /// 设置数据源
        /// </summary>
        /// <param name="result">数据表</param>
        /// <param name="columnname">列名</param>
        /// <param name="columntext">列标题</param>
        /// <param name="columnformat">列格式化</param>
        /// <param name="fieldTotals">汇总字段信息</param>
        public void SetDataSource(DataView result, string[] columnname, string[] columntext, string[] columnformat, object[] fieldTotals, List<CellStyle> cellStyle)
        {
            int colCount = columnname.Length;
            objData = new object[colCount];
            rowCount = result.Count;
            for (int i = 0; i < colCount; i++)
            {
                object[,] objData2 = new object[rowCount, 1];
                for (int j = 0; j < rowCount; j++)
                {
                    objData2[j, 0] = result[j][columnname[i]];
                }
                objData[i] = objData2;
            }
            SetDataSource(objData, columnname, columntext, columnformat, fieldTotals, cellStyle);
        }
        /// <summary>
        /// 设置数据源
        /// </summary>
        /// <param name="result">数据</param>
        /// <param name="columnname">列名</param>
        /// <param name="columntext">列标题</param>
        /// <param name="columnformat">列格式化</param>
        /// <param name="fieldTotals">汇总字段信息</param>
        /// <param name="cellStyle">单元格样式</param>
        public void SetDataSource(object[] result, string[] columnname, string[] columntext, string[] columnformat, object[] fieldTotals, List<CellStyle> cellstyle)
        {
            objData = result;
            int colCount = columnname.Length;
            fieldNames = columnname;
            objHeaders = new object[colCount];
            fieldData = new object[colCount];
            columnFormat = columnformat;
            cellStyle = cellstyle;
            FieldTotals = fieldTotals;
            depth = 1;
            for (int i = 0; i < colCount; i++)
            {
                if (columntext != null && i < columntext.Length)
                {
                    objHeaders[i] = columntext[i];
                    int idepth = columntext[i].Split('|').Length;
                    if (idepth > depth) depth = idepth;
                }
                else
                    objHeaders[i] = columnname[i];
                object[,] objData2 = (object[,])objData[i];
                rowCount = objData2.GetLength(0);
                if (rowCount > 0) fieldData[i] = objData2[0, 0];
            }
        }

        /// <summary>
        /// 从数据源来创建新的表
        /// </summary>
        public void BuildSheet()
        {
            BuildSheet(1, "");
        }

        /// <summary>
        /// 从数据源来创建新的表
        /// </summary>
        /// <param name="sheetIndex">表的索引号</param>
        /// <param name="caption">标题</param>
        public void BuildSheet(int sheetIndex, string caption)
        {
            BuildSheet(sheetIndex, caption, "");
        }

        /// <summary>
        /// 创建新的表
        /// </summary>
        /// <param name="sheetIndex">表的索引号</param>
        /// <param name="caption">标题</param>
        /// <param name="remark">备注</param>
        public void BuildSheet(int sheetIndex, string caption, string remark)
        {
            BuildSheet(sheetIndex, caption, remark, "");
        }

        private Excel.Range CreateColumnTitle(int idxrow, int idxcol)
        {
            Excel.Range oRange = null;
            int titleRowIndex = idxrow++;
            oRange = (Excel.Range)m_objSheet.Cells[titleRowIndex, idxcol];
            if (depth > 1) //多表头
            {
                for (int j = 1; j < depth; j++)
                {
                    oRange.Rows.Insert();
                    idxrow++;
                }
                Excel.Range r = oRange.get_Resize(1, objHeaders.Length);
                oRange.AutoFill(r, Excel.XlAutoFillType.xlFillCopy);
                Excel.Range firstcell = oRange.Offset[-depth + 1];
                Excel.Range r2 = firstcell.get_Resize(depth, objHeaders.Length);
                r.AutoFill(r2, Excel.XlAutoFillType.xlFillCopy);
                r2.Application.ActiveWindow.SplitRow = r.Row;
                try
                {
                    r2.Application.ActiveWindow.FreezePanes = true;
                }
                catch { }
                int idx = 1;
                for (int j = 0; j < objHeaders.Length; j++)
                {
                    string[] titles = objHeaders[j].ToString().Split(
                        new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                    object[,] arr = new object[depth, 1];
                    for (int m = 0; m < titles.Length; m++) arr[m, 0] = titles[m];
                    Excel.Range col = r2.Columns.get_Item(idx++);
                    col.Value = arr;
                }

                for (int j = 1; j <= depth; j++)//合并行单元格
                {
                    Excel.Range row = r2.Rows.get_Item(j);

                    for (int k = 1; k <= r2.Columns.Count;)
                    {
                        Excel.Range merge = null;
                        Excel.Range cell = row.Cells.get_Item(k);
                        int h = 1;
                        Excel.Range cell2 = row.Cells.get_Item(k + h);
                        string title = cell.Value != null ? cell.Value.ToString() : null;
                        string title2 = cell2.Value != null ? cell2.Value.ToString() : null;
                        while ((k + h <= r2.Columns.Count) && title2 != null && title2 == title)
                        {
                            h++;
                            merge = cell.get_Resize(1, h);
                            cell2 = row.Cells.get_Item(k + h);
                            title2 = cell2.Value != null ? cell2.Value.ToString() : null;
                        }
                        if (merge != null)
                        {
                            k += h;
                            merge.Merge(true);
                        }
                        else k++;
                    }
                }
                for (int j = 1; j <= r2.Columns.Count; j++)
                {
                    Excel.Range col = r2.Columns.get_Item(j);
                    for (int k = 1; k <= depth;)//合并列单元格
                    {
                        Excel.Range merge = null;
                        Excel.Range cell = col.Cells.get_Item(k);
                        if (cell.MergeCells)
                        {
                            k++;
                            continue;//行合并后的单元格
                        }
                        int h = 1;
                        Excel.Range cell2 = col.Cells.get_Item(k + h);
                        while ((k + h <= depth) && cell2.Value == null)
                        {
                            h++;
                            merge = cell.get_Resize(h);
                            cell2 = col.Cells.get_Item(k + h);
                        }
                        if (merge != null)
                        {
                            k += h;
                            merge.Merge();
                        }
                        else k++;
                    }
                }
                oRange = (Excel.Range)m_objSheet.Cells[titleRowIndex, idxcol];
                oRange = oRange.get_Resize(depth, objHeaders.Length);
            }
            else
            {
                oRange = oRange.get_Resize(1, objHeaders.Length);
                oRange.Value = objHeaders;
            }
            return oRange;
        }

        /// <summary>
        /// 创建新的表
        /// </summary>
        /// <param name="sheetIndex">表的索引号</param>
        /// <param name="caption">标题</param>
        /// <param name="remark">备注</param>
        /// <param name="sheetName">新表的名称</param>
        public void BuildSheet(int sheetIndex, string caption, string remark, string sheetName)
        {
            if (TempletFile.Length > 0 && System.IO.File.Exists(TempletFile))
            {
                BuildSheetFromTemplet(sheetIndex, caption, remark, sheetName);
                return;
            }
            System.Threading.Thread thisThread = System.Threading.Thread.CurrentThread;
            System.Globalization.CultureInfo originalCulture = thisThread.CurrentCulture;
            try
            {
                thisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                if (sheetIndex > m_objSheets.Count)
                {
                    m_objSheets.Add(m_objOpt, m_objSheets.get_Item(m_objSheets.Count), sheetIndex - m_objSheets.Count, m_objOpt);
                }
                m_objExcel.ErrorCheckingOptions.NumberAsText = false;
                m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(sheetIndex));
                m_objSheet.Activate();
                m_objExcel.ActiveWindow.DisplayGridlines = false;
                //m_objSheet.PageSetup.CenterFooter = @"第 &P 页，共 &N 页";

                int mergecols = objHeaders.Length;
                if (mergecols > 10) mergecols = 10;
                int idxrow = 1;
                if (caption.Length > 0 && !caption.StartsWith("Sheet"))
                {
                    m_objRange = (Excel.Range)m_objSheet.Cells[idxrow++, 1];
                    m_objRange = m_objRange.get_Resize(1, mergecols);
                    m_objRange.Merge(Type.Missing);
                    m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
                    m_objRange.Value = new object[] { caption };
                    m_objFont = m_objRange.Font;
                    m_objFont.Bold = true;
                    m_objFont.Size = 12;
                }
                if (remark.Length > 0)
                {
                    string unit_name = "数量单位='";
                    int idx_unit = remark.IndexOf(unit_name);
                    bool hasunit = (idx_unit >= 0);
                    string str_remark = remark;
                    string str_unit = "";
                    if (hasunit)
                    {
                        int idx_end = remark.IndexOf("'", idx_unit + unit_name.Length);
                        str_unit = remark.Substring(idx_unit, idx_end - idx_unit + 1);
                        if (remark.IndexOf(str_unit + ",") >= 0)
                            str_remark = str_remark.Replace(str_unit + ",", "");
                        else if (remark.IndexOf("," + str_unit) >= 0)
                            str_remark = str_remark.Replace("," + str_unit, "");
                        else
                            str_remark = str_remark.Replace(str_unit, "");
                        str_unit = str_unit.Replace("数量", "").Replace("'", "").Replace("=", "：");
                    }
                    m_objRange = (Excel.Range)m_objSheet.Cells[idxrow++, 1];
                    m_objRange = m_objRange.get_Resize(1, objHeaders.Length - (hasunit ? 1 : 0));
                    m_objRange.Merge(Type.Missing);
                    m_objRange.Value = new object[] { str_remark };
                    m_objFont = m_objRange.Font;
                    m_objFont.Size = 9;
                    if (hasunit)
                    {
                        m_objRange = (Excel.Range)m_objSheet.Cells[idxrow - 1, objHeaders.Length];
                        m_objRange.Value = new object[] { str_unit };
                        m_objFont = m_objRange.Font;
                        m_objFont.Size = 9;
                    }
                }
                if (sheetName.Length > 0)
                    m_objSheet.Name = sheetName;
                m_objRange = CreateColumnTitle(idxrow, 1);
                idxrow = idxrow + depth;
                m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中 ;
                m_objRange.Interior.Color = 0x00FFFF;
                m_objFont = m_objRange.Font;
                m_objFont.Bold = true;
                m_objFont.Size = 9;
                m_objRange.Borders.LineStyle = Excel.XlLineStyle.xlDot;
                m_objRange.Borders.Weight = Excel.XlBorderWeight.xlHairline;
                m_objRange.EntireColumn.AutoFit();
                double pagewidth = m_objRange.Width;
                if (rowCount > 0)
                {
                    m_objRange = (Excel.Range)m_objSheet.Cells[idxrow, 1];
                    m_objRange.Select();
                    try
                    {
                        m_objExcel.ActiveWindow.FreezePanes = true;
                    }
                    catch { }
                    for (int i = 0; i < objHeaders.Length; i++)
                    {
                        m_objRange = (Excel.Range)m_objSheet.Cells[idxrow, i + 1];
                        m_objRange = m_objRange.get_Resize(rowCount + (FieldTotals == null ? 0 : 1), 1);
                        if (columnFormat[i] != null && columnFormat[i].Length > 0)
                        {
                            string numformat = GetFormatString(columnFormat[i]);
                            if (numformat.Length > 0)
                            {
                                m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;//水平居右
                                m_objRange.NumberFormat = numformat;
                            }
                        }
                        else
                        {
                            m_objRange.NumberFormat = "@";
                        }
                        m_objRange.Value = objData[i];
                        m_objFont = m_objRange.Font;
                        m_objFont.Size = 9;
                        m_objRange.Borders.LineStyle = Excel.XlLineStyle.xlDot;
                        m_objRange.Borders.Weight = Excel.XlBorderWeight.xlHairline;
                        if (FieldTotals != null)
                        {
                            m_objRange = (Excel.Range)m_objSheet.Cells[idxrow + rowCount, i + 1];
                            m_objRange.Value = FieldTotals[i];
                        }
                    }
                    if (cellStyle != null)
                    {
                        foreach (CellStyle cs in cellStyle)
                        {
                            m_objRange = (Excel.Range)m_objSheet.Cells[idxrow + cs.RowIndex, cs.ColumnIndex + 1];
                            if (cs.FONTBOLD) m_objRange.Font.Bold = true;
                            if (!cs.FORECOLOR.IsEmpty) m_objRange.Font.Color = cs.FORECOLOR;
                            if (!cs.BACKCOLOR.IsEmpty) m_objRange.Interior.Color = cs.BACKCOLOR;
                            if (cs.VALUE_FORMAT.Length > 0)
                            {
                                if (cs.VALUE_FORMAT.StartsWith(":"))
                                {
                                    m_objRange.Value = cs.VALUE_FORMAT.Substring(1);
                                    m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
                                }
                                else
                                {
                                    string numformat = GetFormatString(cs.VALUE_FORMAT);
                                    if (numformat.Length > 0)
                                    {
                                        m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;//水平居右
                                        m_objRange.NumberFormat = numformat;
                                    }
                                }
                            }
                        }
                    }
                    pagewidth = 0;
                    for (int i = 0; i < objHeaders.Length; i++)
                    {
                        m_objRange = (Excel.Range)m_objSheet.Cells[idxrow, i + 1];
                        m_objRange = m_objRange.get_Resize(rowCount + (FieldTotals == null ? 0 : 1), 1);
                        m_objRange.EntireColumn.AutoFit();
                        pagewidth += m_objRange.Width;
                    }
                }
                if (pagewidth > 470)
                {
                    try
                    {
                        m_objSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                    }
                    catch { }
                }
                if (ProtectPassWord.Length > 0)
                    m_objSheet.Protect(ProtectPassWord, m_objOpt, m_objOpt, m_objOpt, m_objOpt);//密码保护
            }
            catch
            {
                Dispose();
                throw;
            }
            finally
            {
                m_objFont = null;
                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                thisThread.CurrentCulture = originalCulture;
            }
        }

        private string GetFormatString(string str)
        {
            string pformat = "";
            if (str.StartsWith("N") || str.StartsWith("P"))
            {
                if (str.StartsWith("P")) pformat = "%";
                int dot = 0;
                if (str.Length > 1 && int.TryParse(str.Substring(1), out dot))
                {
                    if (dot == 0)
                        pformat = "#,##0" + pformat;
                    else if (dot > 0)
                        pformat = "#,##0." + "".PadRight(dot, '0') + pformat;
                }
            }
            return pformat;
        }

        /// <summary>
        /// 从模板来创建新的表
        /// </summary>
        /// <param name="sheetIndex">表的索引号</param>
        /// <param name="caption">标题</param>
        /// <param name="remark">备注</param>
        /// <param name="sheetName">新表的名称</param>
        /// <param name="templetFile">模板文件</param>
        public void BuildSheet(int sheetIndex, string caption, string remark, string sheetName, string templetFile)
        {
            if (templetFile.Length == 0 || !System.IO.File.Exists(templetFile))
            {
                BuildSheet(sheetIndex, caption, remark, sheetName);
                return;
            }
            Excel.Workbooks m_objBooks_tmp = null;
            Excel._Workbook m_objBook_tmp = null;
            Excel.Sheets m_objSheets_tmp = null;
            Excel._Worksheet m_objSheet_tmp = null;
            try
            {
                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                if (sheetIndex > m_objSheets.Count)
                {
                    m_objSheets.Add(m_objOpt, m_objSheets.get_Item(m_objSheets.Count), sheetIndex - m_objSheets.Count, m_objOpt);
                }
                m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(sheetIndex));
                try
                {
                    m_objBooks_tmp = (Excel.Workbooks)m_objExcel.Workbooks;
                    m_objBook_tmp = (Excel._Workbook)m_objBooks_tmp.Open(templetFile, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                    m_objSheets_tmp = (Excel.Sheets)m_objBook_tmp.Worksheets;
                    m_objSheet_tmp = (Excel._Worksheet)m_objSheets_tmp.get_Item(1);
                    m_objSheet_tmp.Copy(m_objSheet, m_objOpt);
                }
                finally
                {
                    if (m_objBook_tmp != null)
                        m_objBook_tmp.Close(false, m_objOpt, m_objOpt);
                    if (m_objBook_tmp != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook_tmp);
                    m_objBook_tmp = null;
                    m_objBooks_tmp = null;
                    m_objSheet_tmp = null;
                    m_objSheets_tmp = null;
                }
                string sheetname = m_objSheet.Name;
                m_objSheet.Delete();
                m_objSheet = m_objSheets.get_Item(sheetIndex);
                m_objSheet.Name = sheetname;
                BuildSheetFromTemplet(sheetIndex, caption, remark, sheetName);
            }
            catch
            {
                Dispose();
                throw;
            }
            finally
            {
                m_objFont = null;
                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                GC.Collect();
            }
        }

        /// <summary>
        /// 从模板来创建新的表
        /// </summary>
        /// <param name="sheetIndex">表的索引号</param>
        /// <param name="caption">标题</param>
        /// <param name="remark">备注</param>
        /// <param name="sheetName">新表的名称</param>
        private void BuildSheetFromTemplet(int sheetIndex, string caption, string remark, string sheetName)
        {
            try
            {
                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                if (sheetIndex > m_objSheets.Count)
                {
                    m_objSheets.Add(m_objOpt, m_objSheets.get_Item(m_objSheets.Count), sheetIndex - m_objSheets.Count, m_objOpt);
                }
                m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(sheetIndex));
                m_objSheet.Activate();
                m_objRange = (Excel.Range)m_objSheet.Cells[1, 1];
                m_objRange = m_objRange.get_Resize(m_objSheet.Rows.Count, m_objSheet.Columns.Count);
                Excel.Range oRange = null;
                DateTime dtnow = DateTime.Now;
                if (!KeyFlag.ContainsKey("#COUNT")) KeyFlag.Add("#COUNT", rowCount.ToString());
                if (!KeyFlag.ContainsKey("#YYYY")) KeyFlag.Add("#YYYY", dtnow.Year.ToString());
                if (!KeyFlag.ContainsKey("#MM")) KeyFlag.Add("#MM", dtnow.Month.ToString());
                if (!KeyFlag.ContainsKey("#DD")) KeyFlag.Add("#DD", dtnow.Day.ToString());
                foreach (string keyname in KeyFlag.Keys)
                {
                    do
                    {
                        string replacename = '[' + keyname + ']';
                        oRange = m_objRange.Find(replacename, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSearchDirection.xlNext, m_objOpt, m_objOpt);
                        if (oRange != null)
                        {
                            oRange.Value = oRange.Value.ToString().Replace(replacename, KeyFlag[keyname].ToString());
                        }
                    } while (oRange != null);
                }
                for (int i = 0; i < fieldNames.Length; i++)
                {
                    if (fieldNames[i] != null && fieldNames[i].Length > 0)
                    {
                        oRange = m_objRange.Find("[" + fieldNames[i] + "]", m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSearchDirection.xlNext, m_objOpt, m_objOpt);
                        if (oRange != null)
                        {
                            oRange.Value = fieldData[i];
                        }
                    }
                }
                oRange = m_objRange.Find("#标题名称", m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSearchDirection.xlNext, m_objOpt, m_objOpt);
                int mergecols = objHeaders.Length;
                if (mergecols > 10) mergecols = 10;
                if (oRange != null)
                {
                    oRange = oRange.get_Resize(1, mergecols);
                    oRange.Merge(Type.Missing);
                    oRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
                    oRange.Value = new object[] { caption };
                }
                oRange = m_objRange.Find("#查询条件", m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSearchDirection.xlNext, m_objOpt, m_objOpt);
                if (oRange != null)
                {
                    oRange = oRange.get_Resize(1, objHeaders.Length);
                    oRange.Merge(Type.Missing);
                    oRange.Value = new object[] { remark };
                }
                double pagewidth = 0;
                oRange = m_objRange.Find("#行标题块", m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSearchDirection.xlNext, m_objOpt, m_objOpt);
                if (oRange != null)
                {
                    bool onlydata = oRange.Value.ToString().EndsWith("2");
                    object bcolor = oRange.Interior.Color;
                    object fname = oRange.Font.Name;
                    object fcolor = oRange.Font.Color;
                    object fsize = oRange.Font.Size;
                    object fbold = oRange.Font.Bold;
                    object calignment = oRange.HorizontalAlignment;
                    object clinestyle = oRange.Borders.LineStyle;
                    object cweith = oRange.Borders.Weight;
                    oRange = CreateColumnTitle(oRange.Row, oRange.Column);
                    if (!onlydata)
                    {
                        oRange.Interior.Color = bcolor;
                        m_objFont = oRange.Font;
                        m_objFont.Name = fname;
                        m_objFont.Color = fcolor;
                        m_objFont.Bold = fbold;
                        m_objFont.Size = fsize;
                        oRange.HorizontalAlignment = calignment;
                        oRange.Borders.LineStyle = clinestyle;
                        oRange.Borders.Weight = cweith;
                        pagewidth = oRange.Width;
                    }
                }
                oRange = m_objRange.Find("#数据块", m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSearchDirection.xlNext, m_objOpt, m_objOpt);
                if (oRange != null)
                {
                    m_objRange = (Excel.Range)m_objSheet.Cells[oRange.Row + 1, oRange.Column];
                    m_objRange = m_objRange.get_Resize(1, m_objSheet.Columns.Count - oRange.Column + 1);
                    bool onlydata = oRange.Value.ToString().EndsWith("2");
                    object bcolor = oRange.Interior.Color;
                    object fname = oRange.Font.Name;
                    object fcolor = oRange.Font.Color;
                    object fsize = oRange.Font.Size;
                    object fbold = oRange.Font.Bold;
                    object calignment = oRange.HorizontalAlignment;
                    object clinestyle = oRange.Borders.LineStyle;
                    object cweith = oRange.Borders.Weight;
                    if (!onlydata)
                    {
                        for (int i = 1; i < rowCount + (FieldTotals == null ? 0 : 1); i++)
                        {
                            m_objRange.Rows.Insert(Excel.XlDirection.xlDown, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
                        }
                    }
                    if (rowCount > 0)
                    {
                        pagewidth = 0;
                        for (int i = 0; i < objHeaders.Length; i++)
                        {
                            m_objRange = (Excel.Range)m_objSheet.Cells[oRange.Row, oRange.Column + i];
                            m_objRange = m_objRange.get_Resize(rowCount + (FieldTotals == null ? 0 : 1), 1);
                            bool iscomm = TempletFile.EndsWith(CommReportName);
                            if (iscomm && !onlydata)
                            {
                                if (columnFormat[i] != null && columnFormat[i].Length > 0)
                                {
                                    if (columnFormat[i].StartsWith("N"))
                                    {
                                        m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;//水平居右
                                        int dot = 0;
                                        if (columnFormat[i].Length > 1 && int.TryParse(columnFormat[i].Substring(1), out dot))
                                        {
                                            if (dot == 0)
                                                m_objRange.NumberFormat = "#,##0";
                                            else if (dot > 0)
                                                m_objRange.NumberFormat = "#,##0." + "".PadRight(dot, '0');
                                        }
                                    }
                                }
                                else
                                {
                                    m_objRange.NumberFormat = "@";
                                }
                            }
                            m_objRange.Value = objData[i];
                            if (!onlydata)
                            {
                                m_objRange.Interior.Color = bcolor;
                                m_objFont = m_objRange.Font;
                                m_objFont.Name = fname;
                                m_objFont.Color = fcolor;
                                m_objFont.Bold = fbold;
                                m_objFont.Size = fsize;
                                m_objRange.HorizontalAlignment = calignment;
                                m_objRange.Borders.LineStyle = clinestyle;
                                m_objRange.Borders.Weight = cweith;
                            }
                            if (FieldTotals != null)
                            {
                                m_objRange = (Excel.Range)m_objSheet.Cells[oRange.Row + rowCount, oRange.Column + i];
                                m_objRange.Value = FieldTotals[i];
                            }
                            if (iscomm) m_objRange.EntireColumn.AutoFit();
                            pagewidth += m_objRange.Width;
                        }
                    }
                }
                if (sheetName.Length > 0)
                    m_objSheet.Name = sheetName;
                ((Excel.Range)m_objSheet.Cells[1, 1]).Select();

                if (ProtectPassWord.Length > 0)
                    m_objSheet.Protect(ProtectPassWord, m_objOpt, m_objOpt, m_objOpt, m_objOpt);//密码保护
            }
            catch
            {
                Dispose();
                throw;
            }
            finally
            {
                m_objFont = null;
                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                GC.Collect();
            }
        }

        /// <summary>
        /// 打印预览
        /// </summary>
        /// <param name="caption">标题</param>
        public void PrintPreview(string caption)
        {
            try
            {
                //try
                //{
                //    m_objExcel.Run("auto_execute()", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                //}
                //catch { }
                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
                m_objSheet.Activate();
                m_objExcel.Caption = "打印预览 ";
                try
                {
                    m_objSheet.PageSetup.PrintGridlines = false;
                    m_objSheet.PageSetup.CenterHorizontally = true;
                    m_objSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
                }
                catch { }
                m_objExcel.Visible = true;
                m_objSheet.PrintPreview(true);
            }
            finally
            {
                m_objFont = null;
                m_objOpt = null;
                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                m_objBook = null;
                m_objBooks = null;
                m_objExcel = null;
                GC.Collect();
            }
        }

        /// <summary>
        /// 保存Excel
        /// </summary>
        /// <param name="filepath">文件名</param>
        public void Save(String filepath)
        {
            object fileformat = m_objOpt;
            if (filepath.ToLower().EndsWith(".xlsx"))
                fileformat = Excel.XlFileFormat.xlOpenXMLWorkbook;//Excel 2007版本
            else if (filepath.ToLower().EndsWith(".xls"))
                fileformat = Excel.XlFileFormat.xlExcel8;//Excel 2003版本
            else if (filepath.ToLower().EndsWith(".htm") || filepath.ToLower().EndsWith(".html"))
                fileformat = Excel.XlFileFormat.xlHtml;
            Save(filepath, fileformat);
        }

        /// <summary>
        /// 保存Excel
        /// </summary>
        /// <param name="filepath">文件名</param>
        /// <param name="fileformat">文件格式</param>
        public void Save(String filepath, object fileformat)
        {
            try
            {
                if (System.IO.File.Exists(filepath))
                    System.IO.File.Delete(filepath);
                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                if (m_objSheets.Count > 1)
                {
                    m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
                    m_objSheet.Activate();
                }
                //try
                //{
                //    m_objExcel.Run("auto_execute()", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                //}
                //catch { }
                // Save the Workbook and quit Excel.
                m_objBook.SaveAs(filepath, fileformat, m_objOpt,
                    m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            }
            finally
            {
                Dispose();
            }
        }

        /// <summary>
        /// 释放内存
        /// </summary>
        public void Dispose()
        {
            try
            {
                if (m_objBook != null)
                    m_objBook.Close(false, m_objOpt, m_objOpt);
                if (m_objExcel != null)
                    m_objExcel.Quit();
                if (m_objBook != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
                if (m_objBooks != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
                if (m_objSheets != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
                if (m_objExcel != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
                m_objFont = null;
                m_objOpt = null;
                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                m_objBook = null;
                m_objBooks = null;
                m_objExcel = null;
                GC.Collect();
            }
            catch { }
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~ExportExcel()
        {
            Dispose();
        }
    }
    /// <summary>
    /// 单元格样式类
    /// </summary>
    public class CellStyle
    {
        /// <summary>
        /// 行号
        /// </summary>
        public int RowIndex = 0;
        /// <summary>
        /// 列号
        /// </summary>
        public int ColumnIndex = 0;
        /// <summary>
        /// 是否粗体
        /// </summary>
        public bool FONTBOLD = false;
        /// <summary>
        /// 字体颜色
        /// </summary>
        public Color FORECOLOR;
        /// <summary>
        /// 背景颜色
        /// </summary>
        public Color BACKCOLOR;
        /// <summary>
        /// 格式化字符串
        /// </summary>
        public string VALUE_FORMAT = "";

        /// <summary>
        /// 初始化类的新实例
        /// </summary>
        /// <param name="row">行号</param>
        /// <param name="col">列号</param>
        public CellStyle(int row, int col)
        {
            RowIndex = row;
            ColumnIndex = col;
        }
    }
}
